﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Odbc;
using MySql.Data.MySqlClient;

namespace DataBaseTool
{
    public partial class Form7 : Form
    {
        public Form7()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            try
            {
                //DSN:mylink数据源的名称 UID:sql server登录时的身份sa PWD:登录时的密码123456
                //生成连接数据库字符串
                string ConStr = "DSN=sqlanywhere;UID=dba;PWD=sql";
                //定义SqlConnection对象实例
                OdbcConnection odbcCon = new OdbcConnection(ConStr);
                string SqlStr = "select * from t_customer;";

                string database = "joptshop";


                SqlStr = "select * from syscolumns ";

                SqlStr = "select name from sysobjects  where type='U' order by name";

                string tbname = "pbcatedt";

                DataTable tb1 = Tables();

                DataTable tb2 = new DataTable();
                tb2.Columns.Add("table_name");
                tb2.Columns.Add("ct");

                string connstr = "data source=localhost;database=udum;user id=root;password=root;pooling=false;charset=utf8;SslMode = none;";//pooling代表是否使用连接池
                MySqlConnection conn = new MySqlConnection(connstr);

                for (int k = 0; k < tb1.Rows.Count; k++)
                {
                    tbname = tb1.Rows[k]["name"].ToString();

                    SqlStr = "select count(*) as ct  from " + tbname;

                    //SqlStr = "select TABLE_NAME from information_schema.TABLES WHERE TABLE_SCHEMA='" + database + "' and  TABLE_TYPE='BASE TABLE';";

                    OdbcDataAdapter odbcAdapter = new OdbcDataAdapter(SqlStr, odbcCon);
                    DataSet ds = new DataSet();

                    odbcAdapter.Fill(ds);

                    DataTable tables = ds.Tables[0];

                    DataRow r2=tb2.NewRow();
                    r2["table_name"] = tbname;
                    r2["ct"] = tables.Rows[0]["ct"];
                    tb2.Rows.Add(r2);
                    
                    //MessageBox.Show("success");

                    


                    //dataGridView1.DataSource = tables;

                    DataColumnCollection dtc = tables.Columns;


                    DataRowCollection rows = tables.Rows;


                    //string sql = "CREATE TABLE `" + tbname + "` (";
                    string cols = "";
                    for (int i = 0; i < dtc.Count; i++)
                    {
                        string nm = dtc[i].DataType.Name;
                        //sql += "`"+ dtc[i].ColumnName+ "` "+ChangeToMysql(nm)+",";
                        cols += dtc[i].ColumnName + ",";

                    }
                    cols = cols.Substring(0, cols.Length - 1);
                    string sql1 = "";
                    StringBuilder sb = new StringBuilder();

                    for (int j = 0; j < rows.Count; j++)
                    {
                        string vls = "";
                        for (int i = 0; i < dtc.Count; i++)
                        {
                            string tp = dtc[i].DataType.Name;
                            //sql += "`"+ dtc[i].ColumnName+ "` "+ChangeToMysql(nm)+",";
                            string nm = dtc[i].ColumnName;
                            string vv = rows[j][nm].ToString();

                            if (vv.Length == 0)
                            {
                                vv = "null";
                            }
                            else
                            {
                                if (tp.IndexOf("String") > -1)
                                {
                                    vv = "'" + vv + "'";
                                }
                                if (tp.IndexOf("Date") > -1)
                                {
                                    vv = "'" + vv + "'";
                                }
                            }



                            vls += vv + ",";
                        }
                        vls = vls.Substring(0, vls.Length - 1);
                        sql1 = "insert into " + tbname + " (" + cols + ") values (" + vls + ");";
                        //sb.Append(sql1);
                        //Console.WriteLine(sql1+"\r");

                        //Console.WriteLine("SQL:" + sb.ToString());
                        //MySqlCommand cmd3 = new MySqlCommand(sb.ToString(), conn);
                      // MySqlCommand cmd3 = new MySqlCommand(sql1, conn);
                      // try
                      // {
                      //     conn.Open();
                      // }
                      // catch (Exception ex1)
                      // {
                      //
                      // }
                      // try
                      // {
                      //     cmd3.ExecuteNonQuery();
                      // }
                      // catch (Exception ex2)
                      // {
                      //
                      // }
                      // Console.WriteLine("第" + j + "行,语句:" + sql1 + "\r");



                    }



                    Console.WriteLine("write success " + tbname);
                    conn.Close();

                    dataGridView1.DataSource = tb2;


                }

                
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }


        private DataTable Tables()
        {


            DataTable tb1 = new DataTable();
            tb1.Columns.Add("name");


            // DataRow r1 = tb1.NewRow();
            // r1["name"] = "pbcatcol";
            // tb1.Rows.Add(r1);
            //
            // r1 = tb1.NewRow();
            // r1["name"] = "pbcatedt";
            // tb1.Rows.Add(r1);
            //
            // r1 = tb1.NewRow();
            // r1["name"] = "pbcatfmt";
            // tb1.Rows.Add(r1);
            //
            // r1 = tb1.NewRow();
            // r1["name"] = "pbcattbl";
            // tb1.Rows.Add(r1);
            //
            // r1 = tb1.NewRow();
            // r1["name"] = "pbcatvld";
            // tb1.Rows.Add(r1);
            //
            // r1 = tb1.NewRow();
            // r1["name"] = "RowGenerator";
            // tb1.Rows.Add(r1);
            //
            // r1 = tb1.NewRow();
            // r1["name"] = "t_accpay";
            // tb1.Rows.Add(r1);
            //
            // r1 = tb1.NewRow();
            // r1["name"] = "t_accrec";
            // tb1.Rows.Add(r1);
            //
            // r1 = tb1.NewRow();
            // r1["name"] = "t_base";
            // tb1.Rows.Add(r1);

            DataRow r1 = tb1.NewRow();
            r1["name"] = "t_cashier";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_customer";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_dept";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_dw";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_incdec";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_incdec_detail";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_move";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_move_detail";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_movein";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_movein_detail";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_optometry";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_process";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_process_detail";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_purch";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_purch_detail";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_purchorder";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_purchorder_detail";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_sales";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_sales_detail";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_show";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_show_detail";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_vendor";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_wscustomer";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_wssales";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "t_wssales_detail";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tr_clerk";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tr_color";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tr_doctor";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tr_made";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tr_manager";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tr_name";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tr_opt";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tr_payment";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "treg";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "ts_sysdata";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tss_canceluser";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tss_function";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tss_user";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tss_usergrant";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tx_sales";
            tb1.Rows.Add(r1);

            r1 = tb1.NewRow();
            r1["name"] = "tx_sales_detail";
            tb1.Rows.Add(r1);


            return tb1;

        }

        private string ChangeToMysql(string colType) {
            string tp = "";
            //Console.WriteLine("colType:"+colType);
            switch (colType) {

                case "Int32":
                    tp = "int(11)";
                    break;
                case "Int16":
                    tp = "int(11)";
                    break;
                case "String":
                    tp = "varchar(254)";
                    break;
                case "DateTime":
                    tp = "DateTime";
                    break;

                default:
                    tp = colType;
                    break;
            }

            return tp;

        }


        private void mysql_insert() {

            

            

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //ComboBoxItem ci = (ComboBoxItem)comboBox1.SelectedItem;
            
            string tb=comboBox1.SelectedItem.ToString();
            MessageBox.Show(tb);
            try
            {
                //DSN:mylink数据源的名称 UID:sql server登录时的身份sa PWD:登录时的密码123456
                //生成连接数据库字符串
                string ConStr = "DSN=sqlanywhere;UID=dba;PWD=sql";
                //定义SqlConnection对象实例
                OdbcConnection odbcCon = new OdbcConnection(ConStr);
                string SqlStr = "select * from "+tb+";";

                string database = "joptshop";


                //SqlStr = "select * from syscolumns ";

                //SqlStr = "select name from sysobjects  where type='U' order by name";

                //SqlStr = "select TABLE_NAME from information_schema.TABLES WHERE TABLE_SCHEMA='" + database + "' and  TABLE_TYPE='BASE TABLE';";

                OdbcDataAdapter odbcAdapter = new OdbcDataAdapter(SqlStr, odbcCon);
                DataSet ds = new DataSet();

                odbcAdapter.Fill(ds);

                //comboBox1.DataSource = ds.Tables[0];
                //comboBox1.DisplayMember = "name";
                //comboBox1.ValueMember = "name";

                //MessageBox.Show("success");

                // DataTable tables = ds.Tables[0];


                dataGridView1.DataSource = ds.Tables[0];





            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
    }
}
